# 文件名: app/routes/api.py
import math
from datetime import datetime
from tempfile import NamedTemporaryFile
from typing import List, Optional

import openpyxl
from fastapi.responses import FileResponse
from fastapi import APIRouter, Depends, UploadFile, File, BackgroundTasks, HTTPException, Query, Form
from fastapi.params import Body
from fastapi.responses import JSONResponse
from sqlalchemy.orm import Session
import pandas as pd
import io
import os
import json

from starlette.responses import RedirectResponse

from app.routes.websocket import send_progress
from app.utils.logging_config import logger
from app.models.database import get_db, MonitoredUser, Video, SessionLocal, SystemSetting, get_local_time, HotVideo
from app.services.monitor_service import MonitorService
import uuid

router = APIRouter()
monitor_service = MonitorService()
# 存储正在运行的任务
running_tasks = {}


# 上传Excel文件
@router.post("/upload")
async def upload_excel(
        background_tasks: BackgroundTasks,
        file: UploadFile = File(...),
        db: Session = Depends(get_db)
):
    try:
        # 检查文件是否为空
        if not file.filename:
            return JSONResponse(status_code=400, content={"detail": "请选择文件"})

        # 检查文件扩展名
        if not file.filename.endswith(('.xlsx', '.xls')):
            return JSONResponse(status_code=400, content={"detail": "请上传Excel文件(.xlsx或.xls)"})

        # 读取Excel文件内容
        contents = await file.read()
        if len(contents) == 0:
            return JSONResponse(status_code=400, content={"detail": "文件内容为空"})

        try:
            # 首先尝试读取所有表头行，以处理多层表头
            xlsx_file = io.BytesIO(contents)

            # 使用pandas读取时指定header=None，然后手动处理表头
            df_raw = pd.read_excel(xlsx_file, header=None)

            # 确定真正的数据开始行
            header_row = None
            for i in range(min(5, len(df_raw))):  # 检查前5行
                row = df_raw.iloc[i]
                # 查找可能包含"账号博主名称"、"主页链接"等的行
                row_str = ' '.join([str(x) for x in row if not pd.isna(x)])
                if '博主' in row_str or '账号' in row_str or '链接' in row_str:
                    header_row = i
                    break

            if header_row is None:
                header_row = 0  # 如果找不到明确的表头行，假设第一行是表头

            # 重新读取文件，指定正确的表头行
            xlsx_file.seek(0)  # 重置文件指针
            df = pd.read_excel(xlsx_file, header=header_row)

            # 记录数据行的起始偏移量（用于生成序号）
            data_start_row = header_row + 1

        except Exception as e:
            logger.error(f"读取Excel文件出错: {str(e)}", exc_info=True)
            return JSONResponse(status_code=400, content={"detail": f"无法读取Excel文件: {str(e)}"})

        # 清理列名（去除可能的空格和特殊字符）
        df.columns = [str(col).strip() for col in df.columns]

        # 检查并标准化列名
        standardized_columns = {}
        for col in df.columns:
            col_str = str(col).lower()
            if '博主' in col_str or '名称' in col_str or '账号' in col_str:
                standardized_columns['username'] = col
            elif '链接' in col_str or '主页' in col_str or 'url' in col_str:
                standardized_columns['url'] = col
            elif '平台' in col_str:
                standardized_columns['platform'] = col
            elif '内容' in col_str and '加工' not in col_str and '需' not in col_str:
                standardized_columns['content'] = col
            elif '序号' in col_str or '编号' in col_str:
                standardized_columns['sequence'] = col

        # 如果找不到链接列，尝试查找包含URL格式的列
        if 'url' not in standardized_columns:
            for col in df.columns:
                sample_vals = df[col].dropna().head(3).astype(str).tolist()
                # 检查列中的值是否包含常见社交媒体URL模式
                for val in sample_vals:
                    if 'douyin.com' in val or 'tiktok.com' in val or '4wLj' in val:
                        standardized_columns['url'] = col
                        break
                if 'url' in standardized_columns:
                    break

            if 'url' not in standardized_columns:
                return JSONResponse(status_code=400, content={"detail": "无法识别包含链接的列"})

        # 检查是否有有效数据
        if df.empty:
            return JSONResponse(status_code=400, content={"detail": "Excel文件中没有数据"})

        valid_urls = 0
        # 处理每一行
        for index, row in df.iterrows():
            # 获取URL
            url = str(row.get(standardized_columns['url'], ""))
            if not url or pd.isna(url) or url.lower() == 'nan':
                continue

            # 获取分类(内容列)
            category = None
            if 'content' in standardized_columns:
                category = row.get(standardized_columns['content'])
                if pd.isna(category) or str(category).lower() == 'nan':
                    category = None
                else:
                    category = str(category)

            # 确定序号 - 优先使用Excel中的序号列，如果没有则使用行号
            if 'sequence' in standardized_columns and not pd.isna(row[standardized_columns['sequence']]):
                sequence_number = str(row[standardized_columns['sequence']])
            else:
                # 使用实际的Excel行号作为序号（数据开始行+当前索引+1）
                # 加1是为了使序号从1开始而不是0，再加data_start_row是为了匹配Excel的实际行号
                sequence_number = str(data_start_row + index + 1)

            # 处理不同平台的URL
            if "tiktok.com" in url:
                if "@" in url:
                    username = url.split("@")[1].split(" ")[0].split("/")[0].strip()
                    background_tasks.add_task(
                        monitor_service.add_tiktok_user_with_sequence,
                        db, username, sequence_number, category
                    )
                    valid_urls += 1

            elif "douyin.com" in url:
                # 处理标准抖音链接
                if "/user/" in url:
                    sec_uid = url.split("/user/")[1].split(" ")[0].split("?")[0].strip()
                    background_tasks.add_task(
                        monitor_service.add_douyin_user_with_sequence,
                        db, sec_uid, sequence_number, category
                    )
                    valid_urls += 1
                # 处理4wLj开头的抖音短链接
                elif "4wLj" in url:
                    sec_uid_part = url  # 对于短链接，直接使用整个URL
                    background_tasks.add_task(
                        monitor_service.add_douyin_user_with_sequence,
                        db, sec_uid_part, sequence_number, category
                    )
                    valid_urls += 1

            elif "instagram.com" in url:
                username = url.split("instagram.com/")[1].split(" ")[0].split("/")[0].split("?")[0].strip()
                background_tasks.add_task(
                    monitor_service.add_instagram_user_with_sequence,
                    db, username, sequence_number, category
                )
                valid_urls += 1

            elif "kuaishou.com/profile/" in url:
                username = url.split("kuaishou.com/profile/")[1].split("?")[0].split("/")[0].strip()
                background_tasks.add_task(
                    monitor_service.add_kuaishou_user_with_sequence,
                    db, username, sequence_number, category
                )
                valid_urls += 1

        if valid_urls == 0:
            return JSONResponse(status_code=400, content={"detail": "未找到有效的社交媒体URL"})

        return JSONResponse(content={"message": f"文件上传成功，正在处理{valid_urls}个用户数据"})

    except Exception as e:
        logger.error(f"处理Excel文件失败: {str(e)}", exc_info=True)
        return JSONResponse(status_code=400, content={"detail": f"处理文件失败: {str(e)}"})


# 获取所有监控用户
@router.get("/users")
async def get_users(
        page: int = Query(1, ge=1),  # 页码，默认第1页，不小于1
        per_page: int = Query(10, ge=5, le=100),  # 每页条数，默认10条，在5-100之间
        platform: str = Query(None),  # 平台过滤
        search: str = Query(None),  # 搜索关键词
        db: Session = Depends(get_db)
):
    # 构建查询
    query = db.query(MonitoredUser)

    # 应用平台过滤
    if platform:
        query = query.filter(MonitoredUser.platform == platform)

    # 应用搜索关键词
    if search:
        query = query.filter(MonitoredUser.username.ilike(f"%{search}%"))

    # 获取总记录数
    total = query.count()

    # 应用分页
    query = query.order_by(MonitoredUser.created_at.desc())
    query = query.offset((page - 1) * per_page).limit(per_page)

    # 执行查询
    users = query.all()

    # 格式化结果
    result = []
    for user in users:
        profile_info = {}
        try:
            profile_info = json.loads(user.profile_info)
        except:
            pass

        result.append({
            "id": user.id,
            "platform": user.platform,
            "username": user.username,
            "sec_uid": user.sec_uid,
            "user_id": user.user_id,
            "category": user.category,
            "last_video_count": user.last_video_count,
            "last_checked": user.last_checked.isoformat() if user.last_checked else None,
            "created_at": user.created_at.isoformat() if user.created_at else None,
            "profile_info": profile_info,
            "is_open": user.is_open
        })

        # 返回结果包含分页信息
        return {
            "items": result,
            "total": total,
            "page": page,
            "per_page": per_page,
            "pages": (total + per_page - 1) // per_page  # 总页数
        }


# 更新用户分类
@router.put("/users/{user_id}/category")
async def update_user_category(
        user_id: int,
        category_data: dict = Body(...),
        db: Session = Depends(get_db)
):
    user = db.query(MonitoredUser).filter(MonitoredUser.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="用户不存在")

    # 更新分类
    user.category = category_data.get("category")
    user.is_open = category_data.get("is_open")
    db.commit()

    return {"message": "分类已更新", "category": user.category}


# 获取单个用户信息
@router.get("/users/{user_id}")
async def get_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(MonitoredUser).filter(MonitoredUser.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="用户不存在")

    profile_info = {}
    try:
        profile_info = json.loads(user.profile_info)
    except:
        pass

    return {
        "id": user.id,
        "platform": user.platform,
        "username": user.username,
        "sec_uid": user.sec_uid,
        "user_id": user.user_id,
        "category": user.category,
        "last_video_count": user.last_video_count,
        "last_checked": user.last_checked.isoformat() if user.last_checked else None,
        "created_at": user.created_at.isoformat() if user.created_at else None,
        "profile_info": profile_info
    }


# 删除用户
@router.delete("/users/{user_id}")
async def delete_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(MonitoredUser).filter(MonitoredUser.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="用户不存在")

    # 删除用户相关的视频
    videos = db.query(Video).filter(Video.user_id == user_id).all()
    for video in videos:
        if video.local_path and os.path.exists(f"app{video.local_path}"):
            os.remove(f"app{video.local_path}")
        db.delete(video)

    db.delete(user)
    db.commit()

    return {"message": "用户删除成功"}


# 获取视频列表
@router.get("/videos")
async def get_videos(
        page: int = Query(1, ge=1),
        per_page: int = Query(10, ge=5, le=100),
        user_id: int = Query(None),
        platform: str = Query(None),
        status: str = Query(None),
        search: str = Query(None),
        start_date: str = Query(None),  # 起始日期
        end_date: str = Query(None),    # 结束日期
        db: Session = Depends(get_db)
):
    query = db.query(Video)

    # 应用过滤条件
    if user_id:
        query = query.filter(Video.user_id == user_id)

    if platform:
        query = query.filter(Video.platform == platform)

    if status:
        query = query.filter(Video.download_status == status)

    if search:
        query = query.filter(Video.title.ilike(f"%{search}%"))

    if start_date:
        query = query.filter(Video.download_at >= datetime.strptime(start_date, "%Y-%m-%d"))

    if end_date:
        query = query.filter(Video.download_at <= datetime.strptime(end_date, "%Y-%m-%d"))

    # 获取总记录数
    total = query.count()

    # 获取下载总数
    total_download_count = query.filter(Video.download_status == "downloaded").count()

    # 应用分页
    query = query.order_by(Video.download_at.desc())
    query = query.offset((page - 1) * per_page).limit(per_page)

    # 执行查询
    videos = query.all()

    # 格式化结果
    result = []
    for video in videos:
        download_url = []
        try:
            download_url = json.loads(video.download_url)
        except:
            pass

        result.append({
            "id": video.id,
            "user_id": video.user_id,
            "platform": video.platform,
            "video_id": video.video_id,
            "title": video.title,
            "local_path": video.local_path,
            "download_status": video.download_status,
            "last_download_url": video.last_download_url,
            "created_at": video.created_at.isoformat() if video.created_at else None,
            "download_urls": download_url,
            "download_at": video.download_at.isoformat() if video.download_at else None
        })

    return {
        "items": result,
        "total": total,
        "page": page,
        "per_page": per_page,
        "pages": (total + per_page - 1) // per_page,
        "total_download_count": total_download_count  # 返回下载总数
    }


# 使用新地会话防止异步任务中的会话失效问题
def get_new_db():
    new_db = SessionLocal()
    try:
        return new_db
    finally:
        new_db.close()


async def check_user_update(user_id: int, client_id: str = None):
    # 获取新的数据库会话
    async_db = get_new_db()
    user = async_db.query(MonitoredUser).filter(MonitoredUser.id == user_id, MonitoredUser.is_open == True).first()
    if not user:
        logger.info(f"用户不存在: ID={user_id} 或 未启用查询")
        async_db.close()
        return

    try:
        if user.platform == "tiktok":
            await monitor_service.check_tiktok_updates(async_db, user, client_id)
        elif user.platform == "douyin":
            await monitor_service.check_douyin_updates(async_db, user, client_id)
        elif user.platform == "instagram":
            await monitor_service.check_instagram_updates(async_db, user, client_id)
        elif user.platform == "kuaishou":
            await monitor_service.check_kuaishou_updates(async_db, user, client_id)
    except Exception as e:
        logger.info(f"检查更新过程中发生错误: {str(e)}")
    finally:
        # 更新任务状态
        if client_id:
            await send_progress(client_id, {
                "type": "success",
                "message": f"完成用户 {user.username} 的检查"
            })

        logger.info(f"完成用户 {user.username} 的检查")
        async_db.close()


async def check_all_users_update(client_id: str = None):
    # 获取新的数据库会话
    async_db = get_new_db()
    users = async_db.query(MonitoredUser).filter(MonitoredUser.is_open == True).all()

    for user in users:
        try:
            if user.platform == "tiktok":
                await monitor_service.check_tiktok_updates(async_db, user, client_id)
            elif user.platform == "douyin":
                await monitor_service.check_douyin_updates(async_db, user, client_id)
            elif user.platform == "instagram":
                await monitor_service.check_instagram_updates(async_db, user, client_id)
            elif user.platform == "kuaishou":
                await monitor_service.check_kuaishou_updates(async_db, user, client_id)
        except Exception as e:
            logger.error(f"检查用户 {user.username} 更新时发生错误: {str(e)}")

    # 更新任务状态
    if client_id:
        await send_progress(client_id, {
            "type": "success",
            "message": "完成所有平台的检查成功"
        })

    logger.info(f"完成所有平台的检查成功")

    async_db.close()


async def check_platform_users_update(platform: str, client_id: str = None):
    """按平台检查用户更新"""
    # 获取新的数据库会话
    async_db = get_new_db()

    # 获取指定平台的用户
    users = async_db.query(MonitoredUser).filter(MonitoredUser.platform == platform, MonitoredUser.is_open == True).all()

    logger.info(f"开始检查 {platform} 平台的 {len(users)} 个用户")

    # 更新任务进度信息
    if client_id:
        await send_progress(client_id, {
            "type": "info",
            "message": f"开始检查 {platform} 平台的 {len(users)} 个用户"
        })

    for user in users:
        try:
            # 更新任务进度
            if client_id:
                await send_progress(client_id, {
                    "type": "info",
                    "message": f"开始检查用户: {user.username}"
                })
            if platform == "tiktok":
                await monitor_service.check_tiktok_updates(async_db, user, client_id)
            elif platform == "douyin":
                await monitor_service.check_douyin_updates(async_db, user, client_id)
            elif platform == "instagram":
                await monitor_service.check_instagram_updates(async_db, user, client_id)
            elif platform == "kuaishou":
                await monitor_service.check_kuaishou_updates(async_db, user, client_id)

        except Exception as e:
            # 更新任务进度
            if client_id:
                await send_progress(client_id, {
                    "type": "info",
                    "message": f"检查 {platform} 平台用户 {user.username} 时发生错误: {str(e)}"
                })
            logger.error(f"检查 {platform} 平台用户 {user.username} 时发生错误: {str(e)}")

    # 更新任务状态
    if client_id:
        await send_progress(client_id, {
            "type": "success",
            "message": f"完成检查 {platform} 平台用户"
        })

    logger.info(f"完成检查 {platform} 平台的所有用户")
    async_db.close()


# 手动检查更新
@router.api_route("/check-updates", methods=["GET", "POST"])
async def check_updates(
        background_tasks: BackgroundTasks,
        user_id: int = Query(None),
        platform: str = Query(None),
        client_id: Optional[str] = None,
        redirect: bool = True,
        db: Session = Depends(get_db)
):
    # 生成客户端ID（如果未提供）
    if not client_id:
        client_id = str(uuid.uuid4())

    if user_id:
        user = db.query(MonitoredUser).filter(MonitoredUser.id == user_id, MonitoredUser.is_open == True).first()
        if not user:
            raise HTTPException(status_code=404, detail="用户不存在 或 未启用查询")

        # 存储任务信息
        running_tasks[client_id] = {"type": "single_user", "user_id": user_id}

        background_tasks.add_task(check_user_update, user_id, client_id)
        # 根据请求选择返回JSON或重定向
        if redirect:
            return RedirectResponse(url=f"/progress?client_id={client_id}")
        else:
            return JSONResponse(content={"client_id": client_id, "message": f"开始检查用户 {user.username}"})

    elif platform:
        # 按平台检查
        valid_platforms = ["tiktok", "douyin", "instagram", "kuaishou"]
        if platform not in valid_platforms:
            raise HTTPException(status_code=400, detail=f"无效的平台: {platform}")

        # 存储任务信息
        running_tasks[client_id] = {"type": "platform", "platform": platform}

        background_tasks.add_task(check_platform_users_update, platform, client_id)

        # 根据请求选择返回JSON或重定向
        if redirect:
            return RedirectResponse(url=f"/progress?client_id={client_id}")
        else:
            return JSONResponse(content={"client_id": client_id, "message": f"开始检查 {platform} 平台用户"})

    else:
        # 检查所有用户
        running_tasks[client_id] = {"type": "all_users"}

        background_tasks.add_task(check_all_users_update, client_id)

        # 根据请求选择返回JSON或重定向
        if redirect:
            return RedirectResponse(url=f"/progress?client_id={client_id}")
        else:
            return JSONResponse(content={"client_id": client_id, "message": "开始检查所有用户"})


# 添加批量删除视频的API路由
@router.post("/videos-batch")
async def batch_delete_videos(
        video_ids: List[int] = Body(...),
        db: Session = Depends(get_db)
):
    if not video_ids:
        raise HTTPException(status_code=400, detail="未提供视频ID")

    success_count = 0
    error_count = 0

    for video_id in video_ids:
        try:
            video = db.query(Video).filter(Video.id == video_id).first()
            if not video:
                error_count += 1
                continue

            # 从数据库删除记录
            db.delete(video)
            success_count += 1
        except:
            error_count += 1

    # 提交事务
    db.commit()

    return {
        "message": f"批量删除完成: 成功 {success_count} 个，失败 {error_count} 个",
        "success_count": success_count,
        "error_count": error_count
    }


# 添加删除视频的API路由

@router.delete("/videos/{video_id}")
async def delete_video(video_id: int, db: Session = Depends(get_db)):
    video = db.query(Video).filter(Video.id == video_id).first()
    if not video:
        raise HTTPException(status_code=404, detail="视频不存在")

    # 从数据库中删除记录
    db.delete(video)
    db.commit()

    return {"message": "视频删除成功"}


# 批量删除用户路由
@router.delete("/users-batch")
async def batch_delete_users(
        user_ids: List[int] = Body(...),
        db: Session = Depends(get_db)
):
    if not user_ids:
        raise HTTPException(status_code=400, detail="未提供用户ID")

    success_count = 0
    error_count = 0

    for user_id in user_ids:
        try:
            user = db.query(MonitoredUser).filter(MonitoredUser.id == user_id).first()
            if not user:
                error_count += 1
                continue

            # 删除该用户的所有视频
            videos = db.query(Video).filter(Video.user_id == user_id).all()
            for video in videos:
                # 删除视频文件
                if video.local_path:
                    local_file_path = os.path.join("app", video.local_path)
                    try:
                        if os.path.exists(local_file_path):
                            os.remove(local_file_path)

                            # 删除描述文件
                            video_id_str = video.video_id
                            directory = os.path.dirname(local_file_path)
                            desc_file_path = os.path.join(directory, f"{video_id_str}.txt")
                            if os.path.exists(desc_file_path):
                                os.remove(desc_file_path)
                    except Exception as e:
                        logger.error(f"删除文件失败: {str(e)}")

                # 从数据库删除视频记录
                db.delete(video)

            # 从数据库删除用户记录
            db.delete(user)
            success_count += 1
        except Exception as e:
            logger.error(f"删除用户ID {user_id} 失败: {str(e)}")
            error_count += 1

    # 提交事务
    db.commit()

    return {
        "message": f"批量删除完成: 成功 {success_count} 个，失败 {error_count} 个",
        "success_count": success_count,
        "error_count": error_count
    }


# 获取所有设置
@router.get("/settings")
async def get_settings(db: Session = Depends(get_db)):
    settings = db.query(SystemSetting).all()
    return [{"key": s.key, "value": s.value, "description": s.description, "updated_at": s.updated_at} for s in
            settings]


# 获取单个设置
@router.get("/settings/{key}")
async def get_setting(key: str, db: Session = Depends(get_db)):
    setting = db.query(SystemSetting).filter(SystemSetting.key == key).first()
    if not setting:
        raise HTTPException(status_code=404, detail=f"设置 '{key}' 不存在")
    return {"key": setting.key, "value": setting.value, "description": setting.description,
            "updated_at": setting.updated_at}


# 更新设置
@router.put("/settings/{key}")
async def update_setting(
        key: str,
        value: str = Body(..., embed=True),
        db: Session = Depends(get_db)
):
    setting = db.query(SystemSetting).filter(SystemSetting.key == key).first()
    if not setting:
        # 如果设置不存在，创建一个新的
        if key in ["tiktok_monitoring_enabled", "douyin_monitoring_enabled", "instagram_monitoring_enabled",
                   "kuaishou_monitoring_enabled"
                   "hot_videos_enabled", "retry_task_enabled", "use_date_enabled"]:
            if "monitoring" in key:
                description = f"是否启用{key.split('_')[0]}平台监控"
            elif key == "hot_videos_enabled":
                description = "是否启用热门视频功能"
            elif key == "retry_task_enabled":
                description = "是否启用下载失败重试任务"
            elif key == "use_date_enabled":
                description = "是否建立日期文件夹"
            else:
                description = f"设置：{key}"

            setting = SystemSetting(
                key=key,
                value="true",
                description=description
            )
            db.add(setting)
        else:
            raise HTTPException(status_code=404, detail=f"设置 '{key}' 不存在")

    # 验证设置值
    valid, processed_value, error_message = validate_setting_value(key, value)
    if not valid:
        raise HTTPException(status_code=400, detail=error_message)

    # 使用验证后的值
    value = processed_value

    # 更新设置
    setting.value = value
    setting.updated_at = get_local_time()
    db.commit()

    # 如果修改了监控启用状态或检查间隔，重新配置调度器
    if key in ["tiktok_monitoring_enabled", "douyin_monitoring_enabled", "instagram_monitoring_enabled",
               "kuaishou_monitoring_enabled"
               "tiktok_check_interval", "douyin_check_interval", "instagram_check_interval", "kuaishou_check_interval"]:
        from app.main import add_check_task
        try:
            # 重新配置所有平台任务
            add_check_task(db)
        except Exception as e:
            logger.warn(f"重新配置定时任务时出错: {str(e)}")

    # 如果修改了热门视频设置，重新配置热门视频任务
    if key in ["hot_videos_enabled", "hot_videos_check_interval", "hot_videos_daily_hour"]:
        from app.main import add_hot_videos_task
        try:
            # 重新配置热门视频任务
            add_hot_videos_task(db)
        except Exception as e:
            logger.warn(f"重新配置热门视频任务时出错: {str(e)}")

    # 如果修改了重试任务设置，重新配置重试任务
    if key in ["retry_task_enabled", "failed_download_retry_interval", "max_download_retries"]:
        from app.main import add_retry_task
        try:
            # 重新配置重试任务
            add_retry_task(db)
        except Exception as e:
            logger.warn(f"重新配置下载重试任务时出错: {str(e)}")

    return {"key": setting.key, "value": setting.value, "description": setting.description,
            "updated_at": setting.updated_at}


# 修改验证设置值的辅助函数
def validate_setting_value(key, value):
    """验证并处理设置值，返回 (成功标志, 处理后的值, 错误信息)"""
    # 处理布尔值设置
    if key in ["tiktok_monitoring_enabled", "douyin_monitoring_enabled", "instagram_monitoring_enabled",
               "kuaishou_monitoring_enabled"
               "hot_videos_enabled", "retry_task_enabled", "use_date_enabled"]:
        # 确保值是 "true" 或 "false"
        if str(value).lower() not in ["true", "false"]:
            processed_value = "true" if str(value).lower() in ["on", "1", "yes"] else "false"
            return True, processed_value, None
        return True, str(value).lower(), None

    # 处理视频方向设置
    elif key == "download_video_orientation":
        # 确保值是有效的方向选项
        if value not in ["both", "portrait", "landscape"]:
            return False, None, "无效的视频方向设置，必须是 both、portrait 或 landscape"
        return True, value, None

    # 处理普通检查间隔
    elif key in ["tiktok_check_interval", "douyin_check_interval", "instagram_check_interval",
                 "kuaishou_check_interval"]:
        try:
            interval = int(value)
            if interval < 3600:  # 至少1小时
                return False, None, "检查间隔不能小于3600秒"
            return True, str(interval), None
        except ValueError:
            return False, None, "检查间隔必须是整数"

    # 处理下载重试间隔
    elif key == "failed_download_retry_interval":  # 添加对重试间隔的验证
        try:
            interval = int(value)
            if interval < 3600:  # 至少1小时
                return False, None, "重试间隔不能小于3600秒(1小时)"
            return True, str(interval), None
        except ValueError:
            return False, None, "重试间隔必须是整数"

    # 处理最大下载重试次数
    elif key == "max_download_retries":  # 添加对最大重试次数的验证
        try:
            retries = int(value)
            if retries < 1 or retries > 10:
                return False, None, "最大重试次数必须在1-10之间"
            return True, str(retries), None
        except ValueError:
            return False, None, "最大重试次数必须是整数"

    # 处理热门视频检查间隔
    elif key == "hot_videos_check_interval":
        try:
            interval = int(value)
            if interval < 3600:  # 至少1小时
                return False, None, "热门视频检查间隔不能小于3600秒(1小时)"
            return True, str(interval), None
        except ValueError:
            return False, None, "检查间隔必须是整数"

    # 处理热门视频每日时间点
    elif key == "hot_videos_daily_hour":
        try:
            hour = int(value)
            if hour < 0 or hour > 23:
                return False, None, "小时值必须在0-23之间"
            return True, str(hour), None
        except ValueError:
            return False, None, "小时值必须是整数"

    # 处理热门视频拉取页数
    elif key == "hot_videos_pages":
        try:
            pages = int(value)
            if pages < 1 or pages > 10:
                return False, None, "拉取页数必须在1-10之间"
            return True, str(pages), None
        except ValueError:
            return False, None, "拉取页数必须是整数"

    # 处理最大重试次数
    elif key == "max_retries":
        try:
            retries = int(value)
            if retries < 1 or retries > 10:
                return False, None, "重试次数必须在1到10之间"
            return True, str(retries), None
        except ValueError:
            return False, None, "重试次数必须是整数"

    # 处理重试延迟
    elif key == "retry_delays":
        try:
            delays = [int(x) for x in str(value).split(",")]
            if any(d < 1 for d in delays):
                return False, None, "延迟时间不能小于1秒"
            return True, str(value), None
        except ValueError:
            return False, None, "延迟时间必须是逗号分隔的整数列表"

    # 对于其他设置，直接返回字符串值
    return True, str(value), None


@router.get("/template/excel")
async def get_excel_template():
    """生成Excel上传模板"""
    # 创建示例数据
    data = {
        "账号博主名称": [
            "渣渣足球",
            "沉浸足球",
            "足球观众席",
            "足球日记"
        ],
        "主页链接": [
            "https://www.tiktok.com/@username",
            "https://www.douyin.com/user/sec_uid",
            "https://www.instagram.com/username",
            "https://www.kuaishou.com/profile/username"
        ],
        "平台": [
            "tiktok",
            "douyin",
            "instagram",
            "kuaishou"
        ],
        "内容": [
            "足球集锦",
            "治愈风格",
            "美食分享",
            "搞笑"
        ]
    }

    # 创建DataFrame
    df = pd.DataFrame(data)

    # 创建临时文件
    with NamedTemporaryFile(delete=False, suffix=".xlsx") as tmp:
        temp_filename = tmp.name
        # 写入Excel
        df.to_excel(temp_filename, index=False, engine='openpyxl')

        # 使用openpyxl调整列宽
        wb = openpyxl.load_workbook(temp_filename)
        ws = wb.active

        # 设置列宽
        column_widths = {'A': 50, 'B': 120, 'C': 50, 'D': 50}  # 可以根据需要调整宽度
        for col, width in column_widths.items():
            ws.column_dimensions[col].width = width

        # 保存文件
        wb.save(temp_filename)

    # 返回文件
    return FileResponse(
        path=temp_filename,
        filename="社交媒体监控-上传模板.xlsx",
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )


# 添加 TikTok 用户
@router.post("/users/tiktok")
async def add_tiktok_user(
        username: str = Form(...),
        category: str = Form(None),  # 分类参数
        sequence: str = Form(None),  # 序号参数
        db: Session = Depends(get_db)
):
    try:
        # 使用带分类的方法添加用户
        user = await monitor_service.add_tiktok_user_with_sequence(db, username, sequence, category)
        if not user:
            return JSONResponse(status_code=400, content={"error": "无法添加用户，请检查用户名是否正确"})

        # 返回成功信息
        return {"id": user.id, "username": user.username, "platform": user.platform, "category": user.category}
    except Exception as e:
        return JSONResponse(status_code=400, content={"error": str(e)})


# 添加抖音用户
@router.post("/users/douyin")
async def add_douyin_user(
        sec_uid: str = Form(...),
        category: str = Form(None),  # 分类参数
        sequence: str = Form(None),  # 序号参数
        db: Session = Depends(get_db)
):
    try:
        # 使用带分类的方法添加用户
        user = await monitor_service.add_douyin_user_with_sequence(db, sec_uid, sequence, category)
        if not user:
            return JSONResponse(status_code=400, content={"error": "无法添加用户，请检查 sec_uid 是否正确"})

        # 返回成功信息
        return {"id": user.id, "username": user.username, "platform": user.platform, "category": user.category}
    except Exception as e:
        return JSONResponse(status_code=400, content={"error": str(e)})


# 添加 Instagram 用户
@router.post("/users/instagram")
async def add_instagram_user(
        username: str = Form(...),
        category: str = Form(None),  # 分类参数
        sequence: str = Form(None),  # 序号参数
        db: Session = Depends(get_db)
):
    try:
        # 使用带分类的方法添加用户
        user = await monitor_service.add_instagram_user_with_sequence(db, username, sequence, category)
        if not user:
            return JSONResponse(status_code=400, content={"error": "无法添加用户，请检查用户名是否正确"})

        # 返回成功信息
        return {"id": user.id, "username": user.username, "platform": user.platform, "category": user.category}
    except Exception as e:
        return JSONResponse(status_code=400, content={"error": str(e)})


# 添加 快手 用户
@router.post("/users/kuaishou")
async def add_instagram_user(
        username: str = Form(...),
        category: str = Form(None),  # 分类参数
        sequence: str = Form(None),  # 序号参数
        db: Session = Depends(get_db)
):
    try:
        # 使用带分类的方法添加用户
        user = await monitor_service.add_kuaishou_user_with_sequence(db, username, sequence, category)
        if not user:
            return JSONResponse(status_code=400, content={"error": "无法添加用户，请检查用户名是否正确"})

        # 返回成功信息
        return {"id": user.id, "username": user.username, "platform": user.platform, "category": user.category}
    except Exception as e:
        return JSONResponse(status_code=400, content={"error": str(e)})


# 获取抖音热门视频列表
@router.get("/hot-videos")
async def get_hot_videos(
        page: int = Query(1, ge=1),
        per_page: int = Query(12, ge=4, le=100),
        platform: str = Query("douyin"),
        category: str = Query(None),
        sort_by: str = Query("rank"),  # 新增排序字段: rank, publish_time, play_count, like_count
        sort_order: str = Query("asc"),  # 新增排序顺序: asc, desc
        start_date: str = Query(None),  # 新增开始日期
        end_date: str = Query(None),  # 新增结束日期
        db: Session = Depends(get_db)
):
    query = db.query(HotVideo)

    # 应用平台过滤
    if platform:
        query = query.filter(HotVideo.platform == platform)

    # 应用分类过滤
    if category:
        query = query.filter(HotVideo.category == category)

    # 应用日期过滤 - 使用发布时间字段
    if start_date:
        try:
            start_datetime = datetime.now().strptime(start_date, "%Y-%m-%d")
            query = query.filter(HotVideo.publish_time >= start_datetime)
        except ValueError:
            logger.warning(f"无效的开始日期格式: {start_date}")

    if end_date:
        try:
            # 将结束日期设置为当天的23:59:59，以包含整天
            end_datetime = datetime.now().strptime(end_date, "%Y-%m-%d")
            end_datetime = end_datetime.replace(hour=23, minute=59, second=59)
            query = query.filter(HotVideo.publish_time <= end_datetime)
        except ValueError:
            logger.warning(f"无效的结束日期格式: {end_date}")

    # 获取总记录数
    total = query.count()

    # 应用排序
    if sort_by == "publish_time":
        # 按发布时间排序
        if sort_order.lower() == "desc":
            query = query.order_by(HotVideo.publish_time.desc())
        else:
            query = query.order_by(HotVideo.publish_time.asc())
    elif sort_by == "play_count":
        # 按播放次数排序
        if sort_order.lower() == "desc":
            query = query.order_by(HotVideo.play_count.desc())
        else:
            query = query.order_by(HotVideo.play_count.asc())
    elif sort_by == "like_count":
        # 按点赞次数排序
        if sort_order.lower() == "desc":
            query = query.order_by(HotVideo.like_count.desc())
        else:
            query = query.order_by(HotVideo.like_count.asc())
    else:
        # 默认按排名排序
        if sort_order.lower() == "desc":
            query = query.order_by(HotVideo.rank.desc(), HotVideo.created_at.desc())
        else:
            query = query.order_by(HotVideo.rank.asc(), HotVideo.created_at.desc())

    # 应用分页
    query = query.offset((page - 1) * per_page).limit(per_page)

    # 执行查询
    videos = query.all()

    # 格式化结果
    result = []
    for video in videos:
        # 尝试解析下载URL
        download_urls = []
        try:
            if video.download_url:
                download_urls = json.loads(video.download_url)
        except:
            pass

        result.append({
            "id": video.id,
            "platform": video.platform,
            "video_id": video.video_id,
            "title": video.title,
            "cover_url": video.cover_url,
            "duration": video.duration,
            "author": video.author,
            "avatar_url": video.avatar_url,
            "fans_count": video.fans_count,
            "play_count": video.play_count,
            "like_count": video.like_count,
            "publish_time": video.publish_time.isoformat() if video.publish_time else None,
            "video_url": video.video_url,
            "local_path": video.local_path,
            "download_status": video.download_status,
            "rank": video.rank,
            "created_at": video.created_at.isoformat() if video.created_at else None,
            "category": video.category,
            "is_favorite": video.is_favorite,
            "has_download_url": bool(download_urls)
        })

    # 返回结果包含分页信息
    return {
        "items": result,
        "total": total,
        "page": page,
        "per_page": per_page,
        "pages": (total + per_page - 1) // per_page,  # 总页数
        "sort_by": sort_by,
        "sort_order": sort_order,
        "start_date": start_date,
        "end_date": end_date
    }


# 拉取新的热门视频
@router.post("/fetch-hot-videos")
async def fetch_hot_videos(
        background_tasks: BackgroundTasks,
        request_data: dict = Body(...),
        client_id: Optional[str] = None,
        db: Session = Depends(get_db)
):
    # 从请求体中获取参数
    page = request_data.get("page", 1)
    page_size = request_data.get("page_size", 20)
    date_window = request_data.get("date_window", 24)

    # 参数验证
    if page < 1:
        page = 1
    if page_size < 5:
        page_size = 5
    elif page_size > 100:
        page_size = 100
    if date_window < 1:
        date_window = 1
    elif date_window > 72:
        date_window = 72

    # 生成客户端ID
    if not client_id:
        client_id = str(uuid.uuid4())

    # 创建监控服务
    monitor_service = MonitorService(db)

    # 在后台任务中执行拉取热门视频
    background_tasks.add_task(
        monitor_service.fetch_douyin_hot_videos,
        db, page, page_size, date_window, None, client_id
    )

    return {
        "client_id": client_id,
        "message": f"正在获取抖音热门视频 (页码: {page}, 每页: {page_size}, 时间窗口: {date_window}小时)"
    }


# 下载热门视频
@router.post("/hot-videos/{video_id}/download")
async def download_hot_video(
        video_id: int,
        background_tasks: BackgroundTasks,
        client_id: Optional[str] = None,
        db: Session = Depends(get_db)
):
    # 检查视频是否存在
    video = db.query(HotVideo).filter(HotVideo.id == video_id).first()
    if not video:
        raise HTTPException(status_code=404, detail="视频不存在")

    # 检查视频是否已下载
    if video.download_status == "downloaded" and video.local_path:
        return {
            "message": f"视频已下载，无需重复下载",
            "already_downloaded": True,
            "local_path": video.local_path
        }

    # 生成客户端ID
    if not client_id:
        client_id = str(uuid.uuid4())

    # 创建监控服务
    monitor_service = MonitorService(db)

    # 在后台任务中执行下载
    background_tasks.add_task(
        monitor_service.download_hot_video,
        db, video_id, client_id
    )

    return {
        "client_id": client_id,
        "message": f"正在下载视频 {video.title}"
    }


# 收藏/取消收藏热门视频
@router.post("/hot-videos/{video_id}/favorite")
async def toggle_favorite_hot_video(
        video_id: int,
        db: Session = Depends(get_db)
):
    video = db.query(HotVideo).filter(HotVideo.id == video_id).first()
    if not video:
        raise HTTPException(status_code=404, detail="视频不存在")

    # 切换收藏状态
    video.is_favorite = not video.is_favorite
    db.commit()

    return {
        "video_id": video_id,
        "is_favorite": video.is_favorite,
        "message": f"已{'收藏' if video.is_favorite else '取消收藏'}视频"
    }


# 删除热门视频
@router.delete("/hot-videos/{video_id}")
async def delete_hot_video(
        video_id: int,
        db: Session = Depends(get_db)
):
    video = db.query(HotVideo).filter(HotVideo.id == video_id).first()
    if not video:
        raise HTTPException(status_code=404, detail="视频不存在")

    # 如果有本地文件，也一并删除
    if video.local_path:
        try:
            local_file_path = os.path.join("app", video.local_path.lstrip("/static/"))
            if os.path.exists(local_file_path):
                os.remove(local_file_path)
        except Exception as e:
            logger.error(f"删除视频文件失败: {str(e)}")

    # 从数据库中删除记录
    db.delete(video)
    db.commit()

    return {
        "message": "视频删除成功"
    }


# 批量下载热门视频
@router.post("/hot-videos/batch-download")
async def batch_download_hot_videos(
        background_tasks: BackgroundTasks,
        video_ids: List[int] = Body(...),
        client_id: Optional[str] = None,
        db: Session = Depends(get_db)
):
    # 生成客户端ID
    if not client_id:
        client_id = str(uuid.uuid4())

    # 检查视频是否存在并筛选出未下载的视频
    all_videos = db.query(HotVideo).filter(HotVideo.id.in_(video_ids)).all()
    if not all_videos:
        raise HTTPException(status_code=404, detail="未找到指定的视频")

    # 筛选出尚未下载的视频
    to_download_videos = [v for v in all_videos if v.download_status != "downloaded"]
    already_downloaded = len(all_videos) - len(to_download_videos)

    if not to_download_videos:
        return {
            "message": f"所选视频已全部下载完成，无需重复下载",
            "already_downloaded": already_downloaded
        }

    # 获取视频ID列表
    to_download_ids = [v.id for v in to_download_videos]

    # 创建监控服务
    monitor_service = MonitorService(db)

    # 在后台任务中执行下载
    background_tasks.add_task(
        monitor_service.batch_download_hot_videos,
        db, to_download_ids, client_id
    )

    return {
        "client_id": client_id,
        "message": f"正在批量下载 {len(to_download_ids)} 个视频" + (
            f"，已跳过 {already_downloaded} 个已下载视频" if already_downloaded > 0 else ""),
        "video_count": len(to_download_ids),
        "already_downloaded": already_downloaded
    }


# 批量删除热门视频
@router.delete("/hot-videos/batch")
async def batch_delete_hot_videos(
        video_ids: List[int] = Body(...),
        db: Session = Depends(get_db)
):
    if not video_ids:
        raise HTTPException(status_code=400, detail="未提供视频ID")

    success_count = 0
    error_count = 0

    for video_id in video_ids:
        try:
            video = db.query(HotVideo).filter(HotVideo.id == video_id).first()
            if not video:
                error_count += 1
                continue

            # 如果有本地文件，也一并删除
            if video.local_path:
                try:
                    local_file_path = os.path.join("app", video.local_path.lstrip("/static/"))
                    if os.path.exists(local_file_path):
                        os.remove(local_file_path)
                except Exception as e:
                    logger.error(f"删除视频文件失败: {str(e)}")

            # 从数据库中删除记录
            db.delete(video)
            success_count += 1
        except Exception as e:
            logger.error(f"删除视频失败: {str(e)}")
            error_count += 1

    # 提交事务
    db.commit()

    return {
        "message": f"批量删除完成: 成功 {success_count} 个，失败 {error_count} 个",
        "success_count": success_count,
        "error_count": error_count
    }


# 切换视频选中状态
@router.post("/hot-videos/{video_id}/select")
async def toggle_select_hot_video(
        video_id: int,
        db: Session = Depends(get_db)
):
    video = db.query(HotVideo).filter(HotVideo.id == video_id).first()
    if not video:
        raise HTTPException(status_code=404, detail="视频不存在")

    # 切换选中状态
    video.is_selected = not video.is_selected
    db.commit()

    return {
        "video_id": video_id,
        "is_selected": video.is_selected
    }


# 批量选中/取消选中
@router.post("/hot-videos/batch-select")
async def batch_select_hot_videos(
        video_ids: List[int] = Body(...),
        select: bool = Body(...),
        db: Session = Depends(get_db)
):
    db.query(HotVideo).filter(HotVideo.id.in_(video_ids)).update(
        {HotVideo.is_selected: select},
        synchronize_session=False
    )
    db.commit()

    return {
        "message": f"已{'选中' if select else '取消选中'} {len(video_ids)} 个视频"
    }


# 获取视频下载链接
@router.get("/hot-videos/{video_id}/download-urls")
async def get_hot_video_download_urls(
        video_id: int,
        db: Session = Depends(get_db)
):
    video = db.query(HotVideo).filter(HotVideo.id == video_id).first()
    if not video:
        raise HTTPException(status_code=404, detail="视频不存在")

    if not video.download_url:
        # 尝试获取下载链接
        monitor_service = MonitorService(db)
        success = await monitor_service.update_hot_video_details(db, video)
        if not success:
            raise HTTPException(status_code=400, detail="无法获取视频下载链接")
        # 重新从数据库获取视频信息
        db.refresh(video)

    try:
        download_urls = json.loads(video.download_url)
        return {
            "video_id": video_id,
            "title": video.title,
            "urls": download_urls
        }
    except:
        raise HTTPException(status_code=400, detail="解析下载链接失败")
